\documentclass[supercite]{Experimental_Report}

\title{~~~~~~数据结构实验~~~~~~}
\author{崔昊阳}
\school{计算机科学与技术学院}
\classnum{CS2104}
\stunum{U202115415}
\instructor{赵小松}
\date{2021年11月11日}

\usepackage{algorithm, multirow}
\usepackage{algpseudocode}
\usepackage{amsmath}
\usepackage{amsthm}
\usepackage{framed}
\usepackage{mathtools}
\usepackage{subcaption}
\usepackage{xltxtra}
\usepackage{bm}
\usepackage{tikz}
\usepackage{tikzscale}
\usepackage{pgfplots}
%\usepackage{enumerate}
\usepackage{makecell}
\usepackage{caption}
\usepackage{listings}
\lstset{
    language = sql,
    backgroundcolor = \color{white},    % 背景色
    basicstyle = \small\ttfamily,           % 基本样式 + 小号字体
    rulesepcolor= \color{gray},             % 代码块边框颜色
    breaklines = true,                  % 代码过长则换行
    numbers = left,                     % 行号在左侧显示
    numberstyle = \small,               % 行号字体
    keywordstyle = \color{blue}\bfseries,      % 关键字颜色
	identifierstyle=\color{purple}, 		% 标识符颜色
    commentstyle =\color{green},        % 注释颜色
    stringstyle = \color{green},          % 字符串颜色
    % frame = shadowbox,                  % 用（带影子效果）方框框住代码块
    showspaces = false,                 % 不显示空格
    columns = flexible,                    % 字间距固定
    %escapeinside={<@}{@>}              % 特殊自定分隔符：<@可以自己加颜色@>
    % morekeywords = {as},                % 自加新的关键字（必须前后都是空格）
    % deletendkeywords = {compile}        % 删除内定关键字
}


\pgfplotsset{compat=1.16}

\newcommand{\cfig}[3]{
  \begin{figure}[htb]
    \centering
    \includegraphics[width=#2\textwidth]{images/#1.tikz}
    \caption{#3}
    \label{fig:#1}
  \end{figure}
}

\newcommand{\sfig}[3]{
  \begin{subfigure}[b]{#2\textwidth}
    \includegraphics[width=\textwidth]{images/#1.tikz}
    \caption{#3}
    \label{fig:#1}
  \end{subfigure}
}

\newcommand{\xfig}[3]{
  \begin{figure}[htb]
    \centering
    #3
    \caption{#2}
    \label{fig:#1}
  \end{figure}
}

\newcommand{\rfig}[1]{\autoref{fig:#1}}
\newcommand{\ralg}[1]{\autoref{alg:#1}}
\newcommand{\rthm}[1]{\autoref{thm:#1}}
\newcommand{\rlem}[1]{\autoref{lem:#1}}
\newcommand{\reqn}[1]{\autoref{eqn:#1}}
\newcommand{\rtbl}[1]{\autoref{tbl:#1}}

\algnewcommand\Null{\textsc{null }}
\algnewcommand\algorithmicinput{\textbf{Input:}}
\algnewcommand\Input{\item[\algorithmicinput]}
\algnewcommand\algorithmicoutput{\textbf{Output:}}
\algnewcommand\Output{\item[\algorithmicoutput]}
\algnewcommand\algorithmicbreak{\textbf{break}}
\algnewcommand\Break{\algorithmicbreak}
\algnewcommand\algorithmiccontinue{\textbf{continue}}
\algnewcommand\Continue{\algorithmiccontinue}
\algnewcommand{\LeftCom}[1]{\State $\triangleright$ #1}

\newtheorem{thm}{定理}[section]
\newtheorem{lem}{引理}[section]

\colorlet{shadecolor}{black!15}

\theoremstyle{definition}
\newtheorem{alg}{算法}[section]

\def\thmautorefname~#1\null{定理~#1~\null}
\def\lemautorefname~#1\null{引理~#1~\null}
\def\algautorefname~#1\null{算法~#1~\null}

\begin{document}

\maketitle

\clearpage

\pagenumbering{Roman}

\tableofcontents[level=2]

\clearpage

\pagenumbering{arabic}

\section{课程任务概述}
“数据库系统原理实践”是配合“数据库系统原理”课程独立开设的实践课，注重理论与实践相结合。
本课程以MySQL为例，系统性地设计了一系列的实训任务。所有实训任务可以分为如下的几个模块。
\begin{enumerate}
	\item 数据库、表、完整性约束的创建与修改
	\item 数据查询
	\item 数据更新
	\item 视图
	\item 存储过程与事务
	\item 触发器
	\item 用户自定义函数
	\item 安全性控制
	\item 并发控制
	\item 数据库恢复
	\item 数据库设计
	\item 数据库应用系统开发
	\item 数据库管理系统内核实现
\end{enumerate}

课程依托头歌实践教学平台，实验环境为Linux操作系统下的MySQL 8.0.28。在数据库应用开发环节，使用JAVA 1.8。


\newpage
\section{任务实施过程与分析}
本次实践课程在头歌平台进行，实践任务均在平台上提交代码，所有完成的任务、关卡均通过了自动测评。
本次实践最终完成了任务书中\textbf{除了2.15子任务之外的所有子任务}，2.15子任务仅完成了“\textbf{BPlusTreePage的设计}”一关。
下面将重点针对其中的\textbf{2.3子任务、2.7子任务、2.8子任务、2.9子任务、2.11子任务、2.13子任务和2.14子任务}阐述其完成过程中的具体工作。这些子任务的内容概括如下。
\begin{enumerate}
	\item 2.3 数据查询(Select)之一
	\item 2.7 存储过程与事务
	\item 2.8 触发器
	\item 2.9 用户自定义函数
	\item 2.11 并发控制与事务的隔离级别
	\item 2.13 数据库设计与实现
	\item 2.14 数据库应用开发(JAVA篇)
\end{enumerate}

\subsection{数据查询(Select)之一}
使用SQL语言中的select语句进行数据查询是数据库的重要操作之一。本子任务要求使用1条SQL语句实现从数据库中查询要求中
指定的一组数据。本子任务采用某银行的一个金融场景应用的模拟数据库以进行数据库查询。

本子任务一共19个关卡，\textbf{已完成全部的19个关卡}。

\subsubsection{第13关：客户总资产}
本关卡要求用一条SQL语句完成
综合客户表(client)、资产表(property)、理财产品表(finances\_product)、保险表(insurance)、基金表(fund)，
列出所有客户的编号、名称和总资产的任务。其中，总资产命名为total\_property。
总资产为储蓄卡总余额，投资总额，投资总收益的和，再扣除信用卡透支的总金额(信用卡余额即为透支金额)。
客户总资产包括被冻结的资产。

由于客户购买的所有资产的类型和数量信息存储在property表中，而客户购买的资产价格分别存储在各资产的表中。所以，
为了得到每个用户在各类资产上的总额，我们需要依次\textbf{将property表和各类资产表进行自然连接}，使用\textbf{property表中
的pro\_quantity乘以各类资产表中的amount}得到每个用户在各类资产上的总额（注意信用卡的资产为负），之后将得到的
6张派生表\textbf{进行合并}，接着\textbf{将合并后的表和client表进行左连接}以得到c\_id和c\_name属性。这样，我们就
得到了一张所有客户的id，姓名以及总资产的表。最后，我们对这张表\textbf{按id进行聚合}，并\textbf{将表中总资产和为null的用户的总资产设置成0}，
即可得到所有用户的总资产了。

本关卡的代码请见\textbf{附录中的代码1}

\subsubsection{第17关：购买基金的高峰期}
本关卡要求用一条SQL语句完成
查询2022年2月购买基金的高峰期的任务。
至少连续三个交易日，所有投资者购买基金的总金额超过100万(含)，则称这段连续交易日为投资者购买基金的高峰期。
只有交易日才能购买基金,但不能保证每个交易日都有投资者购买基金。
2022年春节假期之后的第1个交易日为2月7日,周六和周日是非交易日，其余均为交易日。
请列出高峰时段的日期和当日基金的总购买金额，按日期顺序排序。总购买金额命名为total\_amount。

我们首先需要计算每个交易日的基金交易总额和这一天之前的交易日的数量。我们首先连接property和fund表，
计算pro\_quantity和f\_amount的乘积，并按pro\_purchase\_time聚合计算其\textbf{总和}。同时，根据交易日的定义计算每个
元组的这一天之前的交易日的数量workday。接着，我们在上述查询所形成的派生表中\textbf{挑选出总额大于1000000的元组}，
并使用row\_number()函数为每一行按workday字段排序\textbf{计算出一个行号}。这样，我们就得到了一个由时间、总额、这一天之前的交易日
数量和行号组成的派生表。我们注意到，当元组的workday字段和行号的差相等时，说明这是一段连续的总额超过1000000的元组。所以，
我们可以\textbf{按照workday字段和行号的差进行聚合计数}，当计数大于等于3，即存在至少连续3个元组满足要求时，我们可以输出这个元组。

本关卡的代码如下：
\begin{lstlisting}
select t3.t as pro_purchase_time, t3.amount as total_amount
from 
(
	select *, count(*) over(partition by t2.workday - t2.rownum) as cnt
	from 
	(
		select *, row_number() over(order by workday) as rownum
		from 
		(
			select
				pro_purchase_time as t, sum(pro_quantity * f_amount) as amount,
				@wd := datediff(pro_purchase_time, "2021-12-31") - 2 * week(pro_purchase_time) workday
			from property, fund, (select @wd) as a
			where pro_purchase_time like "2022-02-\%" and pro_type = 3 and pro_pif_id = f_id 
			group by pro_purchase_time
		) as t1
		where amount > 1000000
	) as t2
) as t3
where t3.cnt >= 3;
\end{lstlisting}

\subsubsection{第19关：以日历表格式显示每日基金购买总金额}
本关卡要求用一条SQL语句完成
以日历表格式列出2022年2月每周每个交易日基金购买总金额。

和前两个关卡一样，我们还要首先计算每个交易日内的总金额。所以，我们首先连接property和fund表，
计算pro\_quantity和f\_amount的乘积，并按pro\_purchase\_time聚合计算其\textbf{总和}。
同时，我们还需要计算总周数和每天是星期几。我们可以使用week()函数来计算周数，用\textbf{week()函数的返回值
减去1月的周数即可得到从2月开始计算的周数}。我们使用\textbf{weekday()函数来计算每天是星期几}。这样，我们就得到了
一个由周数、每天是星期几、每天的总额组成的派生表。接下来，我们就把这个派生表变成日历的形式。我们新建星期一到星期日
的列，使用\textbf{if语句根据id来确定每天应该放入哪个列中}。这样，我们就完成了以日历表格式列出2022年2月每周每个交易日基金购买总金额
的任务。

本关卡的代码如下：
\begin{lstlisting}
select
    `week` as week_of_trading,
    sum(if(`id` = 0, amount, null)) Monday,
    sum(if(`id` = 1, amount, null)) Tuesday,
    sum(if(`id` = 2, amount, null)) Wednesday,
    sum(if(`id` = 3, amount, null)) Thursday,
    sum(if(`id` = 4, amount, null)) Friday
from 
(
    select week(pro_purchase_time) - 5 `week`, weekday(pro_purchase_time) `id`, sum(pro_quantity * f_amount) amount
    from
        property
        join fund
        on pro_pif_id = f_id
    where pro_purchase_time like "2022-02-\%" and pro_type = 3
    group by pro_purchase_time
) as t
group by `week`;
\end{lstlisting}

\subsection{存储过程与事务}
事务是数据库中的一个重要概念，掌握DBMS如何处理事务是非常重要的。本实验通过过三类控制结构进行存储过程的构造，
即基于流程控制语句的存储过程、基于游标的存储过程和基于事务的存储过程。

本子任务一共3个关卡，\textbf{已完成全部的3个关卡}。

\subsubsection{第1关：使用流程控制语句的存储过程}
本关卡要求创建一个存储过程，向表fibonacci插入斐波拉契数列的前n项。

我们可以通过定义一个递归存储过程来实现斐波拉契数列的计算。首先，我们将传入的参数-1。接着定义递归存储过程
cal\_fib。cal\_fib有id, res, pre\_res三个参数，分别代表当前行号、前一行的结果以及前一行的上一行的结果。
在递归开始时，3个参数都置0。递归时，将行号+1，res更新成pre\_res和res之和（如果id<2，则表明此时是斐波那契数列
的前两项，直接置1即可），同时pre\_res更新成res。当行号已经不小于m时，递归停止。

存储过程的代码如下：
\begin{lstlisting}
set m = m - 1;
with recursive cal_fib (id, res, pre_res) as 
(
    select 0, 0, 0
    union all 
    select id + 1, if (id < 2, 1, res + pre_res), res 
    from cal_fib 
    where id < m
) 
select id as n, res as fibn
from cal_fib;
\end{lstlisting}

\subsubsection{第2关：使用游标的存储过程}
本关卡要求使用游标编程存储过程为医院的某科室排夜班值班表。

我们定义两个游标，分别指向医生表和护士表。并定义type用于存放医生类型，head用于存放主任医生。
正常情况下，遍历两个表进行排班。当遍历到星期一时，查询head是否为空，不为空则将主任医生排到周一并将head置空。
当遍历到周末，且医生是主任医生时，将主任医生放入head并顺延即可。存储过程的流程图如图2-1所示。

\begin{figure}[htb]
	\begin{center}
		\includegraphics[scale=0.54]{images/2.7.2流程图.pdf}
		\caption{使用游标的存储过程的流程图}
	\end{center}
\end{figure}

本关卡的代码请见\textbf{附录中的代码2}

\subsubsection{第3关：使用事务的存储过程}
本关卡要求使用事务编写实现转账功能的存储过程。

在使用事务编写实现转账功能的存储过程时，在转账动作前需要判断是否满足转账条件。当转款人不是转出卡的持有人，或收款人是收款卡的持有人，
或转出卡是信用卡，转入卡储蓄卡，或转账金额超过了储蓄卡余额时，不满足条件，需要将返回值置0并回滚事务。如果条件满足，
则进行转账操作，转账成功后，将返回值置1。存储过程的流程图如图2-2所示。

\begin{figure}[H]
	\begin{center}
		\includegraphics[scale=0.54]{images/2.7.3流程图.pdf}
		\caption{使用事务的存储过程的流程图}
	\end{center}
\end{figure}

本关卡的代码请见\textbf{附录中的代码3}

\subsection{触发器}
触发器是一种数据库对象，它通常用于数据库的数据完整性检查。掌握触发器的书写规则是十分重要的。
本任务即要求创建触发器进行合法性检查。

本子任务一共1个关卡，\textbf{已完成全部的1个关卡}。

\subsubsection{第1关：为投资表property实现业务约束规则-根据投资类别分别引用不同表的主码}

本关卡要求为表property(资产表)编写一个触发器，以实现以下完整性业务规则：
\begin{enumerate}
	\item 如果pro\_type = 1, 则pro\_pif\_id只能引用finances\_product表的p\_id
	\item 如果pro\_type = 2, 则pro\_pif\_id只能引用insurance表的i\_id
	\item 如果pro\_type = 3, 则pro\_pif\_id只能引用fund表的f\_id
\end{enumerate}
pro\_type不接受(1,2,3)以外的值。
各投资品种一经销售，不会再改变；
也不需考虑finances\_product，insurance，fund的业务规则(一经销售的理财、保险和基金产品信息会永久保存，不会被删除或修改，即使不再销售该类产品)。

在触发器中，我们使用type来代表pro\_type；使用id来代表pro\_pif\_id。接着，我们\textbf{使用if语句}来判断上述业务规则
是否被破坏，如果业务规则被破坏，则\textbf{使用concat函数}生成错误信息，将错误信息放入error\_message中并抛出异常。具体
判断过程如下。
\begin{enumerate}
	\item 若type不是1,2,3中的任何一个，则进行报错。
	\item 若添加元组的类型为理财产品，且finances\_product表中不含添加的产品，则进行报错。
	\item 若添加元组的类型为保险，且insurance表中不含添加的保险，则进行报错。
	\item 若添加元组的类型为基金，且fund表中不含添加的基金，则进行报错。
\end{enumerate}

触发器的代码如下。
\begin{lstlisting}
declare type int default new.pro_type;
declare id int default new.pro_pif_id;
declare error_message varchar(50);
if type > 3 or type <= 0 then
    set error_message = concat("type ", type, " is illegal!");
elseif type = 1 and id not in (select p_id from finances_product) then
    set error_message = concat("finances product #", id, " not found!");
elseif type = 2 and id not in (select i_id from insurance) then
    set error_message = concat("insurance #", id, " not found!");
elseif type = 3 and id not in (select f_id from fund) then
    set error_message = concat("fund #", id, " not found!");
end if;
if error_message is not null then
    signal sqlstate "45000" set message_text = error_message;
end if;
\end{lstlisting}

\subsection{用户自定义函数}
用户自定义函数能够将特定操作模块化，且方便后续的多次调用，是数据库中的一个重要概念。通过本实验，我们可以了解
用户自定义函数的编写方法。

本子任务一共1个关卡，\textbf{已完成全部的1个关卡}。

\subsubsection{第1关：创建函数并在语句中使用它}
本关卡要求编写一个依据客户编号计算其在本金融机构的存储总额的函数,并在select语句使用这个函数。

函数传入client\_id形参，意为客户编号。为了计算这个客户的所有储蓄卡的存款总额，我们在函数中使用select语句
进行查询。在bank\_card表中过滤b\_type = "储蓄卡"的元组，使用b\_c\_id进行聚合，并找出和client\_id相同
的组进行求和即可。函数的代码如下
\begin{lstlisting}
create function get_deposit(client_id int)
returns numeric(10, 2) 
begin
return 
(
    select sum(b_balance)
    from bank_card
    where b_type = "储蓄卡"
    group by b_c_id
    having b_c_id = client_id
);
\end{lstlisting}

接着，我们应用该函数查询存款总额在100万及以上的客户身份证号，姓名和存储总额(total\_deposit)，
并使结果依存款总额从高到低排序。其SQL语句如下。

\begin{lstlisting}
select *
from 
(
	select c_id_card, c_name, get_deposit(c_id) as total_deposit
	from client
)as t1
where total_deposit >= 1000000
order by total_deposit desc;
\end{lstlisting}

\subsection{并发控制与事务的隔离级别}
数据库在运行过程中，需要保持其一致性，本实验可体现出事务并发访问数据时产生的不一致性。
不一致性问题主要有丢失修改、脏读、不可重复读与幻读等，它可以通过数据库管理系统的并发控制子系统解决，
从而保证数据库中事务的隔离性和一致性。

本子任务一共6个关卡，\textbf{已完成全部的6个关卡}。

\subsubsection{第2关：读脏}
本关卡要求选择合适的事务隔离级别，构造两个事务并发执行时，发生“读脏”现象。

为了使事务1发生脏读，我们首先需要使事务2进行修改操作，再使事务1进行读取。等到事务1读取成功后，对事务2进行回滚即可。
事务1和事务2的时间表如下。
\begin{table}[H]
    \centering
    \caption{事务1和事务2的时间表}
    \begin{tabular}{ccc}
    \toprule
        \textbf{ } & \textbf{事务1} & \textbf{事务2} \\ \hline
        \textbf{1} & 开始 & 开始 \\ 
        \textbf{2} & 等待 & 修改ticket \\ 
        \textbf{3} & 读取ticket & 等待 \\ 
        \textbf{4} & 等待 & 回滚 \\ 
        \textbf{5} & 提交 \\ 
		\bottomrule
    \end{tabular}

\end{table}

事务1和事务2的代码如下。
\begin{lstlisting}[title=事务1]
-- 事务1:
use testdb1;
-- 请设置适当的事务隔离级别
set session transaction isolation level read uncommitted;
start transaction;
-- 时刻2 - 事务1读航班余票,发生在事务2修改之后
-- 添加等待代码，确保读脏
set @n = sleep(1);
select tickets from ticket where flight_no = 'CA8213';
commit;
\end{lstlisting}
\begin{lstlisting}[title=事务2]
-- 事务2
use testdb1;
-- 请设置适当的事务隔离级别
set session transaction isolation level read uncommitted;
start transaction;
-- 时刻1 - 事务2修改航班余票
update ticket set tickets = tickets - 1 where flight_no = 'CA8213';
-- 时刻3 - 事务2 取消本次修改
-- 请添加代码，使事务1在事务2撤销前读脏;
set @n = sleep(2);
rollback;
\end{lstlisting}

\subsubsection{第3关：不可重复读}
本关卡要求选择合适的事务隔离级别，构造两个事务并发执行时，发生“不可重复读”现象。

为了使事务2发生不可重复读，我们需要使事务1在事务2的两次读取之间修改数据。首先，事务1读取余票，接着事务2读取余票。
然后，事务1修改余票后再读取，之后提交。当事务2在事务1修改后再次读取时，就会发生不可重复读。事务1和事务2的时间表如下。
\begin{table}[H]
    \centering
    \caption{事务1和事务2的时间表}
    \begin{tabular}{ccc}
    \toprule
        \textbf{~} & \textbf{事务1} & \textbf{事务2} \\ \hline
        \textbf{1} & 开始 & 开始 \\ 
        \textbf{2} & 读取余票 & 等待 \\ 
        \textbf{3} & 等待 & 读取余票 \\ 
        \textbf{4} & 修改余票 & 等待 \\ 
        \textbf{5} & 读取余票 & 读取余票 \\ 
        \textbf{6} & 等待 & 修改余票 \\ 
        \textbf{7} & 等待 & 提交 \\ 
        \textbf{8} & 读取余票 & ~ \\ 
        \textbf{9} & 提交 \\
		\bottomrule
    \end{tabular}
\end{table}

事务1和事务2的代码见\textbf{附录中的代码4}。

\subsubsection{第5关：主动加锁保证可重复读}
本关卡要求在事务隔离级别较低的read uncommitted情形下，通过主动加锁，保证事务的一致性。

在MySQL中，我们可以使用\textbf{for update对数据加写锁}，使用\textbf{for share对数据加读锁}。为了保证事务1
的可重复读，我们只需要在事务1的前两次读取过程中\textbf{对余票加写锁}即可。事务1读取完成且事务2提交后，事务1可以
不加锁地读取余票，这时事务1读到的余票才会发生变化。事务1和事务2的时间表如下。
\begin{table}[H]
    \centering
    \caption{事务1和事务2的时间表}
    \begin{tabular}{ccc}
    \toprule
        \textbf{~} & \textbf{事务1} & \textbf{事务2} \\ \hline
        \textbf{1} & 开始 & 开始 \\ 
        \textbf{2} & 对余票加写锁 & 等待 \\ 
        \textbf{3} & 读取余票 & 等待 \\ 
        \textbf{4} & 读取余票 & 等待 \\ 
        \textbf{5} & 解除写锁 & 等待 \\ 
        \textbf{6} & 等待 & 修改余票 \\ 
        \textbf{7} & 等待 & 提交 \\ 
        \textbf{8} & 读取余票 & ~ \\ 
        \textbf{9} & 提交 \\
		\bottomrule
    \end{tabular}
\end{table}

事务1和事务2的代码如下。
\begin{lstlisting}[title=事务1]
-- 事务1:
use testdb1;
set session transaction isolation level read uncommitted;
start transaction;
-- 第1次查询航班'MU2455'的余票
select tickets from ticket where flight_no = 'MU2455' for update;
set @n = sleep(5);
-- 第2次查询航班'MU2455'的余票
select tickets from ticket where flight_no = 'MU2455' for update;
commit;
-- 第3次查询所有航班的余票，发生在事务2提交后
set @n = sleep(1);
select * from ticket;
\end{lstlisting}
\begin{lstlisting}[title=事务2]
-- 事务2:
use testdb1;
set session transaction isolation level read uncommitted;
start transaction;
set @n = sleep(1);
-- 在事务1的第1，2次查询之间，试图出票1张(航班MU2455)：
update ticket set tickets = tickets - 1 where flight_no = 'MU2455';
commit;
\end{lstlisting}

\subsection{数据库设计与实现}
本实验要求掌握数据库设计方法与实现过程。数据库设计具体过程可以概括
为在精准需求分析的基础上，设计概念模型，再到逻辑模型，最后进行物理模型
的设计，以此完成数据库的建模工作。

本子任务一共3个关卡，\textbf{已完成全部的3个关卡}。

\subsubsection{第1关：从概念模型到MySQL实现}
本关卡要求根据一个已建好的机票订票系统的逻辑模型，完成MySQL的实现。

根据给定的ER图，我们首先\textbf{为所有的实体建立一张表}，并将所有的属性加入到表之中。接着我们处理实体间的联系。由于在本模型中，
实体间的联系均为\textbf{一对多联系}，所以我们可以通过\textbf{增加一个外键}的方式来表征联系。

本关卡的代码过于长，所以不在实验报告中展示，具体代码见\textbf{附件}。

\subsubsection{第2关：从需求分析到逻辑模型}
本关卡要求根据影院管理系统业务需求描述，完成ER图，并转换成关系模式。
通过需求分析，我们发现实体为电影、顾客、放映厅、排场和电影票。其中顾客与电影之间、电影票与排场之间，
电影与排场之间，排场与放映厅之间存在联系。综上所述，ER图如下。

\begin{figure}[H]
	\begin{center}
		\includegraphics[scale=0.3]{images/er.pdf}
		\caption{影院管理系统的ER图}
	\end{center}
\end{figure}

关系模式如下。
\begin{enumerate}
	\item movie(movie\_ID(pk), title, type, runtime, release\_date, director, starring)
	\item customer(c\_ID(pk), name, phone)
	\item hall(hall\_ID(pk), mode, capacity, location)
	\item schedule(schedule\_ID, date, time, price, number, hall\_ID(fk), movie\_ID(fk))
	\item ticket(ticket\_ID(pk), seat\_num, c\_ID(fk), schedule\_ID(fk))
\end{enumerate}

\subsubsection{制约因素分析与设计}
数据库设计是一个综合的过程,不仅要根据业务需求构建各种模型,
还需要考虑各种制约条件，尽量在满足完整性、安全性的同时，也要达到较高的性能。
在进行需求分析到概念模型转化时，我们还需要充分考虑到安全性问题、法律性问题。
在本实验中，数据库设计所考虑的制约因素主要有如下几点。
\begin{enumerate}
    \item 数据完整性约束：主要包括实体完整性、参照完整性、用户自定义完整性等。它用于确保存储在数据库中的数据是准确可靠的。例如在机票订票系统中航空公司的母港是一个外键，它必须在对应表中的主键集合中出现。
    \item 安全性约束：控制对数据库的访问和操作权限,保证数据库的安全性。例如在机票订票系统中进行用户信息验证时，存储在数据库中的用户名与对应的密码应该采用密文方式存储。
    \item 性能约束：在数据库的设计过程中，我们也需要考虑查询和更新的性能，选择合适的数据库索引等可以提高性能。
    \item 事务约束：确保一组数据库操作要么全部提交，要么全部回滚。保证数据库由一个一致状态迁移到另一个一致状态。
\end{enumerate}
\subsubsection{工程师责任及其分析}
工程师除了进行数据结构设计、系统体系设计、系统模型设计等计算机相关
工作要求外，还需要对该系统的应用场景、法律效力、社会安全风险等问题进行
充分考虑与分析。在数据库设计与实现过程中,数据库工程师主要负有以下责任。
\begin{enumerate}
    \item 需求分析：与业务人员沟通，理解业务需求和数据需求，构建概念数据模型。这是数据库设计的第一步，对后续工作至关重要。
    \item 规范化设计：进行数据库表的规范化设计，消除数据冗余，确保数据完整性。多数情况需要达到第三范式。
    \item 数据库物理设计：在选定的DBMS上，根据逻辑模型构建数据库和表，确定字段名、类型、约束等。
    \item 性能优化：通过索引，调整表结构等手段进行数据库性能优化，以达到较高的查询和更新性能。
    \item 开发数据库应用：开发数据库的管理应用和业务应用程序，以实现对数据库的操作和业务功能。
    \item 数据库测试：对设计的数据库和应用程序进行充分的测试，保证其功能正确性和可靠性。
    \item 数据库上线/升级：将测试通过的数据库上线使用，或对现有数据库进行升级改造。需要制定详细的部署和升级计划。
\end{enumerate}
% 要修改一下
\subsection{数据库应用开发(JAVA篇)}
高级语言的SQL嵌入能够丰富数据库的相关操作，是进行数据库操作的重要方法。
本实验采用Java进行数据库应用开发。通过本实验，我们可以掌握JDBC的简单使用方法。

本子任务一共7个关卡，\textbf{已完成全部的7个关卡}。

\subsubsection{第5关：客户修改密码}

本关卡要求实现修改密码的方法passwd()。passwd()的参数和返回值如下。
\begin{table}[H]
    \centering
    \caption{passwd()的参数}
    \begin{tabular}{cccc}
    \toprule
        \textbf{字段名称} & \textbf{数据类型} & \textbf{约束} & \textbf{说明} \\ \hline
        c\_id & INTEGER & PRIMARY KEY & 客户编号 \\ 
        c\_name & VARCHAR(100) & NOT NULL & 客户名称 \\ 
        c\_mail & CHAR(30) & UNIQUE & 客户邮箱 \\ 
        c\_id\_card & CHAR(20) & UNIQUE NOT NULL & 客户身份证 \\ 
        c\_phone & CHAR(20) & UNIQUE NOT NULL & 客户手机号 \\ 
        c\_password & CHAR(20) & NOT NULL & 客户登录密码 \\
        \bottomrule
    \end{tabular}
\end{table}
passwd()返回：
\begin{enumerate}
    \item 密码修改成功返回1
    \item 用户不存在返回2
    \item 密码不正确返回3
    \item 程序异常（如没能连接到数据库等）返回-1
\end{enumerate}

首先，我们定义3个SQL字符串，分别用于查询用户邮箱是否存在、用户邮箱对应的密码是否正确以及修改用户邮箱对应的密码。
接着，我们使用\textbf{PreparedStatement接口的executeQuery.next}来查找邮箱为该用户邮箱的元组。
若无要求元组，则说明用户不存在，返回2。否则，我们继续查看该元组用户邮箱对应的密码是否正确，
若不是，则说明密码不正确，返回3。若用户邮箱对应的密码正确，则使用\textbf{executeUpdate进行修改}，并返回1。
当出现程序异常时，返回-1。

本关卡的代码见\textbf{附录中的代码5}。

\subsubsection{第6关：事务与转账操作}

本关卡要求编写一个银行卡转账的方法transferBalance()。transferBalance()的参数和返回值如下。
\begin{table}[H]
    \centering
    \caption{transferBalance()的参数}
    \begin{tabular}{cccc}
    \toprule
        \textbf{字段名称} & \textbf{数据类型} & \textbf{约束} & \textbf{说明} \\ \hline
        b\_number & CHAR(30) & PRIMARY KEY & 银行卡号 \\ 
        b\_type & CHAR(20) & 无 & \makecell{银行卡类型\\(储蓄卡/信用卡)} \\ 
        b\_c\_id & INTEGER & NOT NULL FOREIGN KEY & 所属客户编号 \\ 
        b\_balance & NUMERIC(10,2) & NOT NULL & 余额 \\
        \bottomrule
    \end{tabular}
\end{table}
transferBalance()返回boolean值，true表示转帐成功，false表示转账失败。

首先，我们取消自动提交，并设置事务隔离级别为SERIALIZABLE。接着使用\textbf{PreparedStatement接口的executeQuery.next}来
检查转入账户是否存在且符合要求。若符合要求，我们进行转账操作并使用\textbf{connection.commit()提交}，返回True。
若不符合要求，我们进行\textbf{事务的回滚}并返回False。

本关卡的代码见\textbf{附录中的代码6}。

\newpage
\section{课程总结}
在本次数据库系统原理实践中，我完成了14个实训任务的所有子任务，以及实训15中的1个关卡。
在实践过程中，我将数据库原理理论知识和实践相结合，掌握了以MySQL为主的数据库编程技能。
此外，我还学习了使用高级JAVA语言进行实际应用开发的具体思路和操作方法。
通过实践，我初步理解了数据库和数据库管理系统的特点以及与其相关的设计与实现，熟悉了相关建模工具的使用。
通过本次实验，我对数据库系统的理解有了很大的提高，这有利于我在后续课程中使用数据库这一数据管理工具。

在各实训环节中，我主要完成了以下工作。
在数据库、表与完整性约束的定义(Create)实训部分，并掌握了MySQL的表定义语句以及完整性约束的使用。
在表结构与完整性约束的修改(ALTER)实训部分，并掌握了MySQL的表定义修改语句的使用。
在数据查询(Select)实训部分，并对MySQL的select语句有了更深入的理解。
在数据的插入、修改与删除(Insert,Update,Delete)实训部分，并掌握了MySQL的增删改语句。
视图实训部分，掌握了视图这一工具的构建与使用。
在存储过程与事务、触发器和用户自定义函数实训部分，掌握了这些MySQL数据库对象的构建与使用。
在安全性控制实训部分，我掌握了如何进行自主存取控制以提升系统安全性。
在并发控制与事务的隔离级别实训部分，我掌握了解决数据不一致问题的方法。
在备份加日志的数据库恢复实训部分，我掌握了使用日志文件与数据库备份的方法进行数据库恢复的技术。
在数据库设计与实现实训部分，我掌握了数据库系统结构中概念模型和逻辑模型设计与构建，以及对需求的分析。
在JAVA高级语言开发数据库应用实训部分，我掌握了JDBC体系架构和数据库应用的设计与实现。

通过完成上述实践，我深刻感受到了数据库在大数据管理与处理方面的强大应用价值。
特别是在进行高级语言的数据库应用开发时，我了解并掌握了数据库在现实项目中的具体应用。
对于此次课程实践，由于实验报告的书写时间和考试周基本重合，且实验报告的格式要求较为复杂。
我建议提前布置实验报告任务，使同学们可以在考试周开始之前完成。同时我希望可以给出实验报告的\LaTeX{}模板，
减少同学们在调格式时花费的时间。
\newpage
\section{附录}
\subsection{代码1: 2.1第13关的代码}
\begin{lstlisting}
select c_id, c_name, ifnull(sum(pro_property), 0) as total_property
from client left join 
(
	select pro_c_id, pro_quantity * p_amount as pro_property
	from property, finances_product
	where pro_type = 1 and pro_pif_id = p_id
	union all
	select pro_c_id, pro_quantity * i_amount as pro_property
	from property, insurance
	where pro_type = 2 and pro_pif_id = i_id
	union all
	select pro_c_id, pro_quantity * f_amount as pro_property
	from property, fund
	where pro_type = 3 and pro_pif_id = f_id
	union all
	select b_c_id, b_balance as pro_property
	from bank_card
	where b_type = "储蓄卡"	
	union all 
	select b_c_id, -b_balance as pro_property
	from bank_card
	where b_type = "信用卡"	
	union all
	select pro_c_id, pro_income as pro_property
	from property
) a on c_id = pro_c_id
group by c_id
order by c_id;
\end{lstlisting}

\subsection{代码2: 2.7第2关的代码}
\begin{lstlisting}
declare complete, tmp, `week` int default false;
declare doctor, nurse1, nurse2, head char(30);
declare cursor1 cursor for select e_name from employee where e_type = 3;
declare cursor2 cursor for select e_type, e_name from employee where e_type < 3;
declare continue handler for not found set complete = true;
open cursor1;
open cursor2;
while start_date <= end_date do
	fetch cursor1 into nurse1;
	if complete then
		close cursor1;
		open cursor1;
		set complete = false;
		fetch cursor1 into nurse1;
	end if;
	fetch cursor1 into nurse2;
	if complete then
		close cursor1;
		open cursor1;
		set complete = false;
		fetch cursor1 into nurse2;
	end if;
	set `week` = weekday(start_date);
	if `week` = 0 and head is not null then
		set doctor = head;
		set head = null;
	else
		fetch cursor2 into tmp, doctor;
		if complete then
			close cursor2;
			open cursor2;
			set complete = false;
			fetch cursor2 into tmp, doctor;
		end if;
		if `week` > 4 and tmp = 1 then
			set head = doctor;
			fetch cursor2 into tmp, doctor;
			if complete then
				close cursor2;
				open cursor2;
				set complete = false;
				fetch cursor2 into tmp, doctor;
			end if;
		end if;
	end if;
	insert into night_shift_schedule values (start_date, doctor, nurse1, nurse2);
	set start_date = date_add(start_date, interval 1 day);
end while;
\end{lstlisting}

\subsection{代码3: 2.7第3关的代码}
\begin{lstlisting}
declare src_id, dst_id int;
declare src_type, dst_type char(20);
declare src_balance, dst_amount numeric(10, 2) default amount;
select b_c_id, b_balance, b_type
into src_id, src_balance, src_type
from bank_card
where b_number = source_card_id;
select b_c_id, b_type
into dst_id, dst_type
from bank_card
where b_number = dest_card_id;
if src_id != applicant_id or dst_id != receiver_id  or (src_type = "信用卡" and dst_type = "储蓄卡") or (src_type = "储蓄卡" and src_balance < amount) then
	set return_code = 0;
	leave pro;
end if;
if src_type = "信用卡" then
	set amount = -amount;
end if;
if dst_type = "信用卡" then
	set dst_amount = -dst_amount;
end if;
update bank_card set b_balance = b_balance - amount where b_number = source_card_id;
update bank_card set b_balance = b_balance + dst_amount where b_number = dest_card_id;
set return_code = 1;
\end{lstlisting}
\subsection{代码4: 2.11第3关的代码}
\begin{lstlisting}[title=事务1]
-- 事务1:
-- 请设置适当的事务隔离级别
set session transaction isolation level read committed;
-- 开启事务
start transaction;
-- 时刻1 - 事务1读航班余票:
insert into result 
select now(),1 t, tickets from ticket where flight_no = 'CZ5525';
-- 添加等待代码，确保事务2的第一次读取在事务1修改前发生
set @n = sleep(2);
-- 时刻3 - 事务1修改余票，并立即读取:
update ticket set tickets = tickets - 1 where flight_no = 'CZ5525';
insert into result
select now(),1 t, tickets from ticket where flight_no = 'CZ5525';
-- 添加代码，使事务2 的第2次读取在事务1修改之后，提交之前发生
-- set @n = sleep(1);
commit;
-- 时刻6 - 事务1在t2也提交后读取余票
-- 添加代码，确保事务1在事务2提交后读取
set @n = sleep(3);
insert into result
select now(), 1 t, tickets from ticket where flight_no = 'CZ5525';
\end{lstlisting}
\begin{lstlisting}[title=事务2]
-- 事务2
-- 请设置适当的事务隔离级别以构造不可重复读
set session transaction isolation level read committed;
start transaction;
-- 时刻2 - 事务2在事务1读取余票之后也读取余票
-- 添加代码，确保事务2的第1次读发生在事务1读之后，修改之前
set @n = sleep(1);
insert into result 
select now(),2 t, tickets from ticket where flight_no = 'CZ5525';
-- 时刻4 - 事务2在事务1修改余票但未提交前再次读取余票，事务2的两次读取结果应该不同
-- 添加代码，确保事务2的读取时机
set @n = sleep(2);
insert into result 
select now(), 2 t, tickets from ticket where flight_no = 'CZ5525';
-- 事务2立即修改余票
update ticket set tickets = tickets - 1 where flight_no = 'CZ5525';
-- 时刻5 - 事务2 读取余票（自己修改但未交的结果）:
set @n = sleep(1);
insert into result 
select now(), 2 t, tickets from ticket where flight_no = 'CZ5525';
commit;
\end{lstlisting}
\subsection{代码5: 2.14第5关的代码}
\begin{lstlisting}[language=java]
String sql1 = "select * from client where c_mail = ?";
String sql2 = "select * from client where c_mail = ? and c_password = ?";
String sql3 = "update client set c_password = ? where c_mail = ? and c_password = ?";
try 
{
    PreparedStatement pps1 = connection.prepareStatement(sql1);
    pps1.setString(1, mail);
    if(pps1.executeQuery().next())
    {
        PreparedStatement pps2 = connection.prepareStatement(sql2);
        pps2.setString(1, mail);
        pps2.setString(2, password);
        if(pps2.executeQuery().next())
        {
            PreparedStatement pps3 = connection.prepareStatement(sql3);
            pps3.setString(1, newPass);
            pps3.setString(2, mail);
            pps3.setString(3, password);
            pps3.executeUpdate();
            return 1;
        }
        else
        {
            return 3;
        }
    }
    else
    {
        return 2;
    }
} 
catch (SQLException e) 
{
    e.printStackTrace();
}
return -1;
\end{lstlisting}
\subsection{代码6: 2.14第6关的代码}
\begin{lstlisting}
try
{
    connection.setAutoCommit(false);
    connection.setTransactionIsolation(8);
    boolean succeed = false;
    PreparedStatement ps = connection.prepareStatement("select * from bank_card where b_number = ?");
    ps.setString(1, destCard);
    ResultSet res1 = ps.executeQuery();
    connection.commit();
    succeed = res1.next(); // 检查转入账户是否存在
    ps = connection.prepareStatement("select * from bank_card where b_number = ? and b_type = \"储蓄卡\" and b_balance >= ?"); // 检查转出账户是否符合要求
    ps.setString(1, sourceCard);
    ps.setDouble(2, amount);
    ResultSet res2 = ps.executeQuery();
    connection.commit();
    succeed &= res2.next();
    if(succeed)
    {
        Double new_amount1 = res1.getDouble("b_balance") + amount; // 转入账户储蓄卡余额
        if (res1.getString("b_type").equals("信用卡"))
        {
            new_amount1 = res1.getDouble("b_balance") - amount; // 转入账户信用卡余额
        }
        Double new_amount2 = res2.getDouble("b_balance") - amount; // 转出账户余额
        String update_sql = "update bank_card set b_balance = ? where b_number = ?";
        ps = connection.prepareStatement(update_sql);
        ps.setDouble(1, new_amount1);
        ps.setString(2, destCard);
        ps.executeUpdate();
        ps = connection.prepareStatement(update_sql);
        ps.setDouble(1, new_amount2);
        ps.setString(2, sourceCard);
        ps.executeUpdate();
        connection.commit();
    }
    return succeed;
}
catch (SQLException e)
{
    e.printStackTrace();
}
return false;
\end{lstlisting}
\end{document}